﻿namespace SinclairCC.AppVSuite.Search
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using SinclairCC.AppVSuite.Shared;

    internal partial class SearchForm : Form
    {
        private System.Data.SqlClient.SqlConnection connAppV;
        private ColumnSorter sorter;

        public SearchForm()
        {
            this.InitializeComponent();

            this.sorter = new ColumnSorter();
            this.packageNameList.ListViewItemSorter = this.sorter;
            this.packageDetailsList.ListViewItemSorter = this.sorter;
            this.applicationsList.ListViewItemSorter = this.sorter;
        }

        private System.Collections.Specialized.StringCollection ApplicationNames
        {
            get
            {
                System.Collections.Specialized.StringCollection returnValue = new System.Collections.Specialized.StringCollection();
                System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter("SELECT DISTINCT name FROM APPLICATIONS", this.connAppV);
                System.Data.DataTable appData = new DataTable("appNames");
                adapter.Fill(appData);
                adapter.Dispose();

                foreach (System.Data.DataRow groupRow in appData.Rows)
                {
                    string appName = (string)groupRow["name"];
                    returnValue.Add(appName);
                }

                return returnValue;
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
#if DEBUG
            this.textAppName.Text = "App-V";
#endif
            this.EnsureConnectionStringExists();

            DataTable appData = this.FindApplicationsByName(this.textAppName.Text.Trim());

            this.applicationsList.BeginUpdate();
            this.SizeListViewColumns(this.applicationsList);
            this.applicationsList.EndUpdate();

            this.packageNameList.BeginUpdate();
            this.SizeListViewColumns(this.packageNameList);
            this.packageNameList.EndUpdate();

            this.packageDetailsList.BeginUpdate();
            this.SizeListViewColumns(this.packageDetailsList);
            this.packageDetailsList.EndUpdate();
            
            this.textAppName.AutoCompleteSource = AutoCompleteSource.CustomSource;
            this.textAppName.AutoCompleteMode = AutoCompleteMode.Suggest;
            this.textAppName.AutoCompleteCustomSource = new AutoCompleteStringCollection();

            System.Collections.Specialized.StringCollection appNames = this.ApplicationNames;
            string[] appNameArray = new string[appNames.Count];
            appNames.CopyTo(appNameArray, 0);
            this.textAppName.AutoCompleteCustomSource.AddRange(appNameArray);
        }

        /// <summary>
        /// Ensures the application has the information it needs to connect to the App-V database.
        /// </summary>
        private void EnsureConnectionStringExists()
        {
            // Read the connection string from the registry, prompting the user for any missing information.
            DialogResult connectionPropertiesResult = System.Windows.Forms.DialogResult.None;
            string connectionString = null;
            do
            {
                try
                {
                    connectionString = Settings.ConnectionString;
                }
                catch (System.ArgumentNullException)
                {
                    connectionString = null;
                }

                if (connectionString == null)
                {
                    connectionPropertiesResult = new ConnectionProperties().ShowDialog(this);
                }
            }
            while ((connectionString == null) && (connectionPropertiesResult != System.Windows.Forms.DialogResult.Cancel));

            // If the connection string is successfully obtained from the registry, connect to the
            // App-V database and initialize the form's ListView control. Otherwise, close the form.
            if (connectionString != null)
            {
                this.connAppV = new System.Data.SqlClient.SqlConnection(connectionString);
                try
                {
                    this.connAppV.Open();
                }
                catch (System.Data.SqlClient.SqlException sqlError)
                {
                    StringBuilder errorMessage = new StringBuilder("An SQL error occurred while opening a connection to the database.");
                    if (sqlError.Message.Length > 0)
                    {
                        errorMessage.Append(System.Environment.NewLine);
                        errorMessage.Append(System.Environment.NewLine);
                        errorMessage.Append(sqlError.Message);
                    }

                    MessageBox.Show(this, errorMessage.ToString(), "App-V Search", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, 0);
                }
                catch (System.InvalidOperationException invalidOpError)
                {
                    StringBuilder errorMessage = new StringBuilder("An invalid operation error occurred while opening a connection to the database.");
                    if (invalidOpError.Message.Length > 0)
                    {
                        errorMessage.Append(System.Environment.NewLine);
                        errorMessage.Append(System.Environment.NewLine);
                        errorMessage.Append(invalidOpError.Message);
                    }

                    MessageBox.Show(this, errorMessage.ToString(), "App-V Search", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, 0);
                }
            }
            else
            {
                this.Close();
            }
        }

        /// <summary>
        /// Handles the Click event for the Configure Connection button.
        /// </summary>
        /// <param name="sender">
        /// The button being clicked.
        /// </param>
        /// <param name="e">
        /// Data specific to this event.
        /// </param>
        private void ClickConfigureConnectionButton(object sender, EventArgs e)
        {
            DialogResult result = new ConnectionProperties().ShowDialog(this);
            if (result == System.Windows.Forms.DialogResult.OK)
            {
                this.EnsureConnectionStringExists();
            }
        }

        private void SearchForm_FormClosing(object sender, FormClosingEventArgs e)
        {
            if ((this.connAppV != null) && (this.connAppV.State == ConnectionState.Open))
            {
                this.connAppV.Close();
            }
        }

        private void SizeListViewColumns(ListView lv)
        {
            string dummyColumnName = Guid.NewGuid().ToString();
            lv.Columns.Add(dummyColumnName, dummyColumnName, 0);
            foreach (ColumnHeader clm in lv.Columns)
            {
                clm.AutoResize(ColumnHeaderAutoResizeStyle.HeaderSize);
                int headerWidth = clm.Width;
                clm.AutoResize(ColumnHeaderAutoResizeStyle.ColumnContent);
                int contentWidth = clm.Width;
                clm.Width = Math.Max(headerWidth, contentWidth);
            }

            lv.Columns.RemoveByKey(dummyColumnName);
        }

        private void ClickSearchButton(object sender, EventArgs e)
        {
            this.EnsureConnectionStringExists();

            DataTable appData = this.FindApplicationsByName(this.textAppName.Text.Trim());

            this.applicationsList.BeginUpdate();
            this.applicationsList.Items.Clear();

            if ((appData != null) && (appData.Rows.Count > 0))
            {
                foreach (DataRow row in appData.Rows)
                {
                    string name = (string)row["name"];
                    string version = (string)row["version"];
                    bool enabled = (bool)row["enabled"];
                    string path = (string)row["path"];
                    ListViewItem newItem = new ListViewItem(name);
                    newItem.SubItems.Add(version);
                    newItem.SubItems.Add(enabled.ToString());
                    newItem.SubItems.Add(path);
                    this.applicationsList.Items.Add(newItem);
                }
            }
            else
            {
                ListViewItem newItem = new ListViewItem("No applications found.");
                this.applicationsList.Items.Add(newItem);
            }

            this.SizeListViewColumns(this.applicationsList);
            this.applicationsList.EndUpdate();
            
            DataTable pkgData = this.FindPackagesByName(this.textAppName.Text.Trim());

            this.packageNameList.BeginUpdate();
            this.packageNameList.Items.Clear();
            if ((pkgData != null) && (pkgData.Rows.Count > 0))
            {
                foreach (DataRow row in pkgData.Rows)
                {
                    string name = (string)row["name"];
                    int id = (int)row["package_id"];
                    ListViewItem newItem = new ListViewItem(name);
                    newItem.Tag = id;
                    this.packageNameList.Items.Add(newItem);
                }
            }
            else
            {
                ListViewItem newItem = new ListViewItem("No packages found.");
                this.packageNameList.Items.Add(newItem);
            }

            this.SizeListViewColumns(this.packageNameList);
            this.packageNameList.EndUpdate();

            this.packageDetailsList.BeginUpdate();
            this.packageDetailsList.Items.Clear();
            this.SizeListViewColumns(this.packageDetailsList);
            this.packageDetailsList.EndUpdate();
        }

        private void LoadPackageDetails()
        {
            this.packageDetailsList.BeginUpdate();
            this.packageDetailsList.Items.Clear();

            foreach (ListViewItem item in this.packageNameList.SelectedItems)
            {
                if ((item.Tag != null) && ((this.connAppV != null) && (this.connAppV.State == ConnectionState.Open)))
                {
                    int packageId = (int)item.Tag;

                    System.Data.SqlClient.SqlCommand selectLocationCmd = this.connAppV.CreateCommand();

                    selectLocationCmd.CommandText = "SELECT version_number, version_guid, package_file, create_date FROM PACKAGE_VERSIONS WHERE (package_id = @package_id) ORDER BY create_date";
                    selectLocationCmd.Parameters.Add("package_id", SqlDbType.Int);
                    selectLocationCmd.Parameters["package_id"].Value = packageId;

                    System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(selectLocationCmd);
                    System.Data.DataTable resultTable = new DataTable();
                    adapter.Fill(resultTable);
                    adapter.Dispose();

                    selectLocationCmd.Dispose();

                    foreach (DataRow row in resultTable.Rows)
                    {
                        int versionNumber = (int)row["version_number"];
                        Guid versionGuid = (Guid)row["version_guid"];
                        string packageFile = (string)row["package_file"];
                        DateTime creationDate = DateTime.MinValue;
                        if (row["create_date"] != DBNull.Value)
                        {
                            creationDate = (DateTime)row["create_date"];
                        }

                        ListViewItem newItem = new ListViewItem(versionNumber.ToString());
                        newItem.SubItems.Add(packageFile);
                        newItem.SubItems.Add(string.Format("{0:G}", creationDate));
                        newItem.SubItems.Add(versionGuid.ToString("D"));
                        this.packageDetailsList.Items.Add(newItem);
                    }
                }
            }

            this.SizeListViewColumns(this.packageDetailsList);
            this.packageDetailsList.EndUpdate();
        }
        
        private DataTable FindApplicationsByName(string appName)
        {
            if ((this.connAppV != null) && (this.connAppV.State == ConnectionState.Open))
            {
                System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter("SELECT app_id, name, version, enabled, app_group_id FROM APPLICATIONS WHERE name LIKE '%" + appName + "%' ORDER BY name, version, app_group_id", this.connAppV);
                System.Data.DataTable appData = new DataTable("appData");
                adapter.Fill(appData);
                adapter.Dispose();

                adapter = new System.Data.SqlClient.SqlDataAdapter("SELECT APPLICATIONS.app_id, APPLICATIONS.name, APPLICATIONS.version, APPLICATIONS.enabled, APPLICATIONS.app_group_id FROM APPLICATION_GROUPS INNER JOIN APPLICATIONS ON APPLICATION_GROUPS.app_group_id = APPLICATIONS.app_group_id WHERE (APPLICATION_GROUPS.name LIKE N'%" + appName + "%') ORDER BY name, version, app_group_id", this.connAppV);
                System.Data.DataTable groupData = new DataTable("groupData");
                adapter.Fill(groupData);
                adapter.Dispose();

                appData.Merge(groupData);

                if (appData.Rows.Count > 0)
                {
                    IEnumerable<DataRow> uniqueRecords = appData.AsEnumerable().Distinct(DataRowComparer.Default);

                    appData = System.Data.DataTableExtensions.CopyToDataTable<DataRow>(uniqueRecords);

                    appData.Columns.Add("path", typeof(string));

                    foreach (DataRow row in appData.Rows)
                    {
                        row["path"] = this.GetApplicationPath(row["app_group_id"]);
                    }
                }

                appData.Columns.Remove("app_group_id");
                appData.AcceptChanges();

                return appData;
            }
            else
            {
                return null;
            }
        }

        private DataTable FindPackagesByName(string name)
        {
            if ((this.connAppV != null) && (this.connAppV.State == ConnectionState.Open))
            {
                System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(string.Format("SELECT package_id, name FROM PACKAGES WHERE (name LIKE '%{0}%') ORDER BY name", name), this.connAppV);
                System.Data.DataTable appData = new DataTable("packageData");
                adapter.Fill(appData);
                adapter.Dispose();
                return appData;
            }
            else
            {
                return null;
            }
        }

        private string GetApplicationPath(object groupId)
        {
            if (groupId == System.DBNull.Value)
            {
                return default(string);
            }

            System.Text.StringBuilder returnValue = new System.Text.StringBuilder();

            System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter("SELECT name, parent_id FROM APPLICATION_GROUPS WHERE app_group_id = " + groupId.ToString(), this.connAppV);
            DataTable tableTable = new DataTable();
            tableTable.TableName = "query_results";
            adapter.Fill(tableTable);
            adapter.Dispose();

            for (int i = 0; i < tableTable.Rows.Count; i++)
            {
                DataRow r = tableTable.Rows[i];
                returnValue.Append(this.GetApplicationPath(r["parent_id"]));
                if (r["parent_id"] != System.DBNull.Value)
                {
                    returnValue.Append('\\');
                }

                returnValue.Append((string)r["name"]);
            }

            return returnValue.ToString();
        }

        private void SelectedPackageChanged(object sender, EventArgs e)
        {
            this.LoadPackageDetails();
        }

        private void SearchTextBoxKeyUp(object sender, KeyEventArgs e)
        {
            if (e.KeyData == Keys.Enter)
            {
                this.btnSearch.PerformClick();
                e.Handled = true;
            }
        }

        private void ClickListViewColumn(object sender, ColumnClickEventArgs e)
        {
            ListView lv = sender as ListView;
            if (lv != null)
            {
                switch (lv.Sorting)
                {
                    case System.Windows.Forms.SortOrder.Ascending:
                        lv.Sorting = System.Windows.Forms.SortOrder.Descending;
                        break;
                    case System.Windows.Forms.SortOrder.Descending:
                        lv.Sorting = System.Windows.Forms.SortOrder.Ascending;
                        break;
                    default:
                        lv.Sorting = System.Windows.Forms.SortOrder.Ascending;
                        break;
                }

                this.sorter.CurrentColumn = e.Column;
                this.sorter.ToggleSortOrder();
                lv.SuspendLayout();
                lv.Sort();
                lv.ResumeLayout();
            }
        }

        private void FormClosedEvent(object sender, FormClosedEventArgs e)
        {
            if (this.connAppV != null)
            {
                if (this.connAppV.State == ConnectionState.Open)
                {
                    this.connAppV.Close();
                }

                this.connAppV.Dispose();
            }
        }
    }
}
